package com.crm.util;

import java.io.File;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.crm.model.BaseModel;
import com.crm.model.ConsultInfoManage;
import com.crm.model.entity.ConsultInfoValueKey;
import com.google.gson.JsonObject;

import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelImportUtil {
	

	public static final String baseLocation = "excelLocation" + File.separator;
	
	public static final String consultLoction = baseLocation + "consult";
	/**
	 * 将Excel数据转换为Map
	 * 
	 * @param excelFile
	 * @param map
	 * @return
	 * @throws EncryptedDocumentException
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	public static List<Map<String, Object>> Excel2Map(File excelFile, Map<String, String> headMap)
			throws EncryptedDocumentException,  IOException, InvalidFormatException {
		List<Map<String, Object>> mapList = new ArrayList<>();
		Workbook workbook = WorkbookFactory.create(excelFile);
		Sheet sheet = workbook.getSheetAt(0);
		Map<Integer, String> index2Map = validatePro(sheet, headMap,new ArrayList<>());// 列的位置---中文别名
		if (index2Map.size() > 0) {
			int trLength = sheet.getLastRowNum();
			for (int i = 2; i <= trLength; i++) {//i = 2 Excel表格数据从第三行开始
				Row row = sheet.getRow(i);
				boolean rowIsBlank = rowIsBlank(row, index2Map.keySet()); 
				if (rowIsBlank) {
					Map<String, Object> pro2Val = new HashMap<>();// 行为空添加一个空的Map
					mapList.add(pro2Val);
					continue;
				}
				Map<String, Object> pro2Val = new HashMap<>();
				Iterator<Integer> iterator = index2Map.keySet().iterator();
				while (iterator.hasNext()) {
					int index = iterator.next();
					pro2Val.put(headMap.get(index2Map.get(index)), getCellValue(row.getCell(index)).trim());
					
				}
				mapList.add(pro2Val);
			}
		}
		workbook.close();
		//反向循环删除mapList集合中为空的map 遇到第一个不为空的跳出循环
		for(int i=mapList.size()-1;i>=0;i--){
			if(mapList.get(i)==null||mapList.get(i).size()==0){
				mapList.remove(i);
			}else{
				break;
			}
		}
		return mapList;
	}
	/**
	 * 将Excel数据转换为对像
	 * @param excelFile
	 * @param headMap
	 * @param T
	 * @return
	 * @throws Exception 
	 */
	public static List<?extends BaseModel> Excel2Object(File excelFile, Map<String, String> headMap,Class<?> T) throws Exception{
		List<Map<String,Object>> mapList =Excel2Map(excelFile,headMap);
		List<BaseModel> dataList = new ArrayList<>();
		for(Map<String,Object> dataMap:mapList){
			Object obj =BeanUtil.transMap2Bean(dataMap, T);
			if(obj instanceof BaseModel){
				dataList.add((BaseModel)obj);
			}
		}
		return dataList;
	}
	/**
	 * 
	 * @param mapList
	 * @param T
	 * @return
	 * @throws Exception
	 */
	public static List<?extends BaseModel> map2Object(List<Map<String,Object>> mapList,Class<?> T) throws Exception{
		List<BaseModel> dataList = new ArrayList<>();
		for(Map<String,Object> dataMap:mapList){
			Object obj =BeanUtil.transMap2Bean(dataMap, T);
			if(obj instanceof BaseModel){
				dataList.add((BaseModel)obj);
			}
		}
		return dataList;
	}

	@SuppressWarnings("unchecked")
	public static void main(String[] args) throws Exception {
		List<Map<String, Object>> mapList = ExcelImportUtil.Excel2Map(
				new File("C:\\Users\\Administrator\\Desktop\\客户资源信息\\咨询工作台录入.xlsx"),
				ConsultInfoValueKey.consultHeadtMap);
		for (Map<String, Object> dataMap : mapList) {
			if (dataMap.containsKey("studentSex")) {
				String sex = (String) dataMap.get("studentSex");
				dataMap.put("studentSex", "男".equals(sex) ? "0" : "女".equals(sex) ? "1" : null);
			}
		}
		List<ConsultInfoManage> consultInfoList = (List<ConsultInfoManage>) ExcelImportUtil.map2Object(mapList,
				ConsultInfoManage.class);
		for (ConsultInfoManage info : consultInfoList) {
			System.out.println(JsonUtil.toJsonStr(info));
		}
//		^\[[[:print:]]*\{\"brand\"\:\"fab06f37-84dd-41cd-b185-f9b9e9df55f5\"\,\"school\"\:\[[a-zA-Z0-9\-\,\"]*\"abe4121d-5996-4d47-ab98-ca70523e44d2\"[a-zA-Z0-9\-\,\"]*\]\}[[:print:]]*\]$
//		^\[[[:print:]]*\{\"brand\"\:\"fab06f37-84dd-41cd-b185-f9b9e9df55f5\"\,\"school\"\:\[[a-zA-Z0-9\-\,\"]*\"abe4121d-5996-4d47-ab98-ca70523e44d2\"[a-zA-Z0-9\-\,\"]*\]\}[[:print:]]*\]$
		StringBuilder brandSchool = new StringBuilder();
		brandSchool.append("^\\[[[:print:]]*\\{\\\"brand\\\"\\:\\\"");
		brandSchool.append("fab06f37-84dd-41cd-b185-f9b9e9df55f5");
		brandSchool.append("\\\"\\,\\\"school\\\"\\:\\[[a-zA-Z0-9\\-\\,\\\"]*\\\"");
		brandSchool.append("abe4121d-5996-4d47-ab98-ca70523e44d2");
		brandSchool.append("\\\"[a-zA-Z0-9\\-\\,\\\"]*\\]\\}[[:print:]]*\\]$");
		System.out.println(brandSchool.toString());
		
	}

	/**
	 * 获取单元格当前最大的宽度
	 * @param cellWidthMap
	 * @param value
	 * @param cellIndex
	 * @return
	 */
	private static Map<Integer,Integer> mapColumnWidth(Map<Integer,Integer> cellWidthMap,String value,Integer cellIndex){
		if(cellWidthMap.get(cellIndex)==null){
			cellWidthMap.put(cellIndex, StringUtils.isBlank(value)?4*512:4>value.toString().length()?4*512:value.toString().length()*512);
		}else{
			Integer cellTempWidth = cellWidthMap.get(cellIndex);
			Integer cellCurrentWidth =StringUtils.isBlank(value)?4*512:4>value.toString().length()?4*512:value.toString().length()*512;
			if(cellCurrentWidth>cellTempWidth){
				cellWidthMap.put(cellIndex, cellCurrentWidth);
			}
		}
		return cellWidthMap;
	}
	/**
	 * 将值转换为写入单元格的值
	 * 
	 * @param object
	 * @return
	 */
	private static String getWriteCellVal(Object obj) {
		String val = "";
		if (obj == null) {
			return val;
		}
		if (obj instanceof Date) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			val = sdf.format((Date) obj);
		} else {
			val = obj.toString();
		}
		return val;
	}

	/**
	 * 验证第二行数据
	 */
	private static Map<Integer, String> validatePro(Sheet sheet, Map<String, String> map,List<String> recordLackHeadList) {
		// map 中文别名 <-->属性名称
		Map<Integer, String> index2Map = new HashMap<>();
		Row row = sheet.getRow(1); //验证第二行数据
		int tdLength = row.getLastCellNum();
		for (int i = 0; i < tdLength; i++) {
			String key = getCellValue(row.getCell(i)).trim();//去掉空格 可能存在空格
			if (map.containsKey(key)) {
				index2Map.put(i, key);
			}else{
				System.out.println("不存在的key:"+key);
			}
		}
		if (map.size() > index2Map.size()) { // 如果循环完之后不一致 缺失字段
			Iterator<String> iteratorKey = map.keySet().iterator();
			while(iteratorKey.hasNext()){//记录不存在的字段
				String key = iteratorKey.next();
				if(!index2Map.containsValue(key)){
					recordLackHeadList.add(key);
				}
			}
			index2Map.clear();
		}
		return index2Map;
	}

	/**
	 * 验证这一行的数据是否为空
	 * 
	 * @param row
	 * @return
	 */
	private static boolean rowIsBlank(Row row, Set<Integer> indexSet) {
		List<Integer> list = new ArrayList<>();
		boolean flag = false;
		if (row == null) {
			flag = true;
			return flag;
		}
		Iterator<Integer> iterator = indexSet.iterator();
		while (iterator.hasNext()) {
			Integer key = iterator.next();
			list.add(key);
			Cell cell = row.getCell(key);
			if (cell == null || StringUtils.isBlank(getCellValue(cell))) {
				list.remove((Object) key);
			}
		}
		flag = list.size() > 0 ? false : true;
		return flag;
	}

	/**
	 * 返回单元格数据
	 * 
	 * @param cell
	 * @return
	 */
	private static String getCellValue(Cell cell) {
		if (cell == null) {
			return "";
		}
		String result = new String();
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型
			if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
				short format = cell.getCellStyle().getDataFormat();
				SimpleDateFormat sdf = null;
				if (format == 14 || format == 31 || format == 57 || format == 58) {
					sdf = new SimpleDateFormat("yyyy-MM-dd");
				} else if (format == 20 || format == 32) {
					sdf = new SimpleDateFormat("HH:mm");
				} else {
					sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				}
				double value = cell.getNumericCellValue();
				Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
				result = sdf.format(date);
			} else {
				double value = cell.getNumericCellValue();
				CellStyle style = cell.getCellStyle();
				DecimalFormat format = new DecimalFormat();
				String temp = style.getDataFormatString();
				// 单元格设置成常规
				if (temp.equals("General")) {
					format.applyPattern("#");
				}
				result = format.format(value);
			}
			break;
		case HSSFCell.CELL_TYPE_STRING:// String类型
			result = cell.getRichStringCellValue().toString().trim();
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			result = "";
		default:
			result = "";
			break;
		}
		return result.trim();
	}
	
	/**
	 * 
	 * 
	 * @param cell
	 * @return
	 */
	private static String getCellValue(Cell cell,boolean numberFlag) {
		if (cell == null) {
			return "";
		}
		String result = new String();
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型
			if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
				short format = cell.getCellStyle().getDataFormat();
				SimpleDateFormat sdf = null;
				if (format == 14 || format == 31 || format == 57 || format == 58) {
					sdf = new SimpleDateFormat("yyyy-MM-dd");
				} else if (format == 20 || format == 32) {
					sdf = new SimpleDateFormat("HH:mm");
				} else {
					sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				}
				double value = cell.getNumericCellValue();
				Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
				result = sdf.format(date).trim();
			} else {
				double value = cell.getNumericCellValue();
				if(numberFlag){
					result = String.valueOf(value).trim();
				}else{
					CellStyle style = cell.getCellStyle();
					DecimalFormat format = new DecimalFormat();
					String temp = style.getDataFormatString();
					// 单元格设置成常规
					if (temp.equals("General")) {
						format.applyPattern("#");
					}
					result = format.format(value).trim();
				}
				
			}
			break;
		case HSSFCell.CELL_TYPE_STRING:// String类型
			result = cell.getRichStringCellValue().toString().trim();
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			result = "";
		default:
			result = "";
			break;
		}
		return result.trim();
	}
	// 验证Excel表头数据
	/**
	 * 
	 * @param excelFile
	 * @param headMap  excel表头<--->表头别名
	 * @param recordLackHeadList  记录缺失的字段
	 * @return
	 */
	public static Boolean checkExcelHeadRow(File excelFile, Map<String, String> headMap,List<String> recordLackHeadList) {
		Boolean flag = true;
		Workbook workbook;
		try {
			workbook = WorkbookFactory.create(excelFile);
			Sheet sheet = workbook.getSheetAt(0);
			Map<Integer, String> index2Map = validatePro(sheet, headMap,recordLackHeadList);
			flag = index2Map.size() > 0 ? true : false;
			workbook.close();// 关闭流
		} catch (Exception e) {
			e.printStackTrace();
			flag = false;
		}
		return flag;
	}
	
	/**
	 * 验证学生导入信息
	 * 
	 * @param excelFile excel文件
	 * @param headMap   excel表头<--->表头别名
	 * @param dataSource 验证
	 * @return
	 * @throws IOException
	 * @throws InvalidFormatException
	 * @throws EncryptedDocumentException
	 */
	public static Map<String,Object> checkImprotExcelData(File excelFile, Map<String, String> headMap)
			throws EncryptedDocumentException, IOException, InvalidFormatException {
		Map<String,Object> errorResultMap =new LinkedHashMap<>();
		List<String> headLackList = new ArrayList<>();
		if(!checkExcelHeadRow( excelFile,headMap,headLackList)){
			errorResultMap.put("result", "lackHead");//表头缺失
			String headLackStr = Arrays.toString(headLackList.toArray());
			errorResultMap.put("headLackList", headLackStr.substring(1, headLackStr.lastIndexOf("]")));
			return errorResultMap; 
		}
		List<Map<String, Object>> mapList = Excel2Map(excelFile,headMap);
		if(mapList.size()==0){
			errorResultMap.put("result", "emptyData");//无数据
			return errorResultMap; 
		}
		if(mapList.size()>1000){
			errorResultMap.put("result", "rowTooMuch");//行数超过1000行
			return errorResultMap; 
		}
		Integer rowIndex = 2;//Excel数据从第三行开始
		for (Map<String, Object> dataMap : mapList) {
			++rowIndex;
//			checkConsultData(errorResultMap,dataMap,headMap,rowIndex);
		}
		if(errorResultMap.size()>0){
			errorResultMap.put("result", "errorDataFormat");//数据不合法
		}else{
			errorResultMap.put("result", "success");//数据效验成功
		}
		return errorResultMap;
	}
	

	/**
	 * 验证一行数据 出现不合法数据并标记上
	 * 
	 * @param errorResultMap 存放数据错误信息
	 * @param DataMap  数据
	 * @param headMap  excel表头<--->表头别名
	 * @author rowIndex  行的位置
	 * @return
	 */

	private static Map<String,Object> checkConsultData( Map<String,Object> errorResultMap, Map<String, Object> dataMap,
			Map<String, String> headMap,Integer rowIndex) {
/*		
		name2Pro.put("部门名称", "departmentName1");
		name2Pro.put("电话归属地", "phoneBelong");
		
		name2Pro.put("品牌名称", "brandName");
		name2Pro.put("媒体来源名称", "studentAttrName1");
		name2Pro.put("咨询者类型名称", "studentAttrName2");
		name2Pro.put("关键词", "keyword");
		name2Pro.put("对话页面链接", "pageUrl");
		
		name2Pro.put("姓名", "studentName");
		name2Pro.put("电话", "studentPhone");
		name2Pro.put("性别", "studentSex");
		name2Pro.put("微信", "weChat");
		name2Pro.put("年龄", "age");
		name2Pro.put("QQ", "tengXun");
		name2Pro.put("民族", "nation");
		name2Pro.put("其他联系方式", "ortherphone");
		name2Pro.put("通信地址", "phoneAddress");
		name2Pro.put("工作地址", "workSpace");
		name2Pro.put("邮箱", "email");
		name2Pro.put("紧急联系人", "emergencyContact");
		name2Pro.put("紧急联系人联系方式", "emergencyContactMode");
		name2Pro.put("最高学历", "highestEducation");
		name2Pro.put("毕业院校", "graduateInstitutions");
		
		name2Pro.put("备注", "notes");
		name2Pro.put("对话记录", "conversation");
		
		name2Pro.put("项目级别名称", "projectLevelName");
		name2Pro.put("项目名称", "projectName");*/
		
		boolean flag = true;
		String errorMsg = "";
		String departmentName1 = (String) dataMap.get("departmentName1");
		String phoneBelong = (String) dataMap.get("phoneBelong");
		String brandName = (String) dataMap.get("brandName");
		String studentAttrName1 = (String) dataMap.get("studentAttrName1");
		String studentAttrName2 = (String) dataMap.get("studentAttrName2");
		String keyword = (String) dataMap.get("keyword");
		String pageUrl = (String) dataMap.get("pageUrl");
		String studentName = (String) dataMap.get("studentName");
		String studentPhone = (String) dataMap.get("studentPhone");
		String studentSex = (String) dataMap.get("studentSex");
		String age = (String) dataMap.get("age");
		String QQ = (String) dataMap.get("tengXun");
		String nation = (String) dataMap.get("nation");
		String ortherphone = (String) dataMap.get("ortherphone");
		String phoneAddress = (String) dataMap.get("phoneAddress");
		String email = (String) dataMap.get("email");
		String stuWeixin = (String) dataMap.get("stuWeixin");
		String workSpace = (String) dataMap.get("workSpace");
		String emergencyContact = (String) dataMap.get("emergencyContact");
		String emergencyContactMode = (String) dataMap.get("emergencyContactMode");
		String highestEducation = (String) dataMap.get("highestEducation");
		String graduateInstitutions = (String) dataMap.get("graduateInstitutions");
		String notes = (String) dataMap.get("notes");
		String conversation = (String) dataMap.get("conversation");
		String projectLevelName = (String) dataMap.get("projectLevelName");
		String projectName = (String) dataMap.get("projectName");
		if(dataMap==null||dataMap.size()==0){//过滤空白行
			errorResultMap.put(String.valueOf(rowIndex), "空白行");
			return errorResultMap;
		}
		if (StringUtils.isBlank(departmentName1)) {
			errorMsg = "信息归属地不能为空";
			flag = false;
		}
		
		if (flag && StringUtils.isBlank(brandName)) {// 品牌不能为空
			errorMsg = "招生品牌不能为空";
			flag = false;
		}
		
		if (flag && StringUtils.isBlank(studentAttrName2)) {// 咨询者类型不能为空
			errorMsg="咨询者类型不能为空";
			flag = false;
		}
		
		if (flag && StringUtils.isBlank(studentAttrName1)) {// 媒体来源不能为空
			errorMsg ="媒体来源不能为空";
			flag = false;
		}
		
		if (flag && StringUtils.isBlank(keyword)) {// 关键词不能为空
			errorMsg = "关键词不能为空";
			flag = false;
		}
		if (flag && StringUtils.isBlank(projectName)) {// 项目名称不能为空
			errorMsg = "咨询课程不能为空";
			flag = false;
		}
		if (flag && StringUtils.isBlank(projectLevelName)) {// 项目级别不能为空
			errorMsg = "咨询课程级别不能";
			flag = false;
		}
		if (flag && StringUtils.isBlank(studentName)) {// 学生姓名不能为空
			errorMsg =  "学生姓名不能为空";
			flag = false;
		}
		if(flag&&StringUtils.isBlank(highestEducation)){
			errorMsg =  "最高学历不能为空";
			flag = false;
		}
		if (flag&&!CheckUtil.Sex(studentSex)) {// 性别
			errorMsg = StringUtils.isBlank(studentSex)?"性别不能为空":"性别只能为男或女";
			flag = false;
		}
		
		if (flag&&!CheckUtil.Mobile(studentPhone)) {// 手机号
			errorMsg = StringUtils.isBlank(studentPhone)?"手机号不能为空":"手机号不合法";
			flag = false;
		}
		
		if (flag&&!CheckUtil.Number(age)) {// 年龄
			errorMsg = StringUtils.isBlank(age)?"年龄不能为空":"年龄不是数字";
			flag = false;
		}
		if(flag&&StringUtils.isNotBlank(QQ)){
			flag = CheckUtil.QQnumber(QQ);
			errorMsg = flag?"":"QQ号不合法";
		}
		
		if(!flag){
			errorResultMap.put(String.valueOf(rowIndex), errorMsg);
		}
		return errorResultMap;
	}



}
